<!DOCTYPE HTML>

<?php 
	session_start(); 
	
	if(isset($_SESSION['id'], $_SESSION['vorname'], $_SESSION['name'])){
		$id = $_SESSION['id'];
		$vorname = $_SESSION['vorname'];
		$name = $_SESSION['name'];
	}

?>
<?php if ($_SESSION['workstation']>0) : ?> 
<html>

<head>
<meta http-equiv="Content-type" content="text/html;charset=UTF-8">
<link href='./images/favicon.ico' rel='shortcut icon' type='image/png'>
<link rel="stylesheet" type="text/css" href="./css/index.css">
<title>Mecha</title>
</head>


<body id="fondo">
<div id="cuerpo">
<fieldset style="width: 1000px; color: #FFF;";>
    <?php
        include 'header.php';
    ?>
<div id="subcuerpo">
	<table id='statistics'>
		<tr>
		<td class='statistic_el'>Last user registered:</td><td><?php LastUser() ?></td>
		</tr>
		<tr>
			<td class='statistic_el'>Countries affected until today:</td><td><?php CountriesAffected() ?> </td>
		</tr>
		<tr>
			<td class='statistic_el'>People exposed in every mission:</td><td><?php PeopleAffected() ?></td>
		</tr>
		<tr>
			<td class='statistic_el'>Top 3 Best Company Heroes:</td><td><?php BestHero() ?> </td>
		</tr>

	</table>
</div>


	<?php
		include 'footer.php';
	?>
</fieldset>
</div>
</body>
<?php else : header("location:index.php");?>
<?php endif; ?>
<?php 
function LastUser() {
	$sql = "USE web_db";
    $link = mysqli_connect('localhost', 'root', '');
    $result = mysqli_query($link,$sql);
    
    $sql = "SELECT name,vorname
			FROM staff
			WHERE id<>'root'
			ORDER BY admission_date
			DESC
			LIMIT 1";
    $result = mysqli_query($link,$sql);
    $row = mysqli_fetch_row($result);

    echo "$row[0], $row[1] ";
}

function CountriesAffected() {
	$sql = "USE web_db";
    $link = mysqli_connect('localhost', 'root', '');
    $result = mysqli_query($link,$sql);
    
	$sql = "SELECT p.name, m.miss_nr
			FROM country p, country_mission pm, mission m
			WHERE p.code=pm.code AND pm.mission_nr=m.miss_nr";

    $result = mysqli_query($link,$sql);
    $aux = "";

    while ($row = mysqli_fetch_row($result)) {
    	$aux = $aux . "$row[0] (mission - $row[1]), ";
	}
	$aux = substr($aux, 0, -2);
	$aux = $aux . ".";
	echo $aux;

}

function PeopleAffected() {
	$sql = "USE web_db";
    $link = mysqli_connect('localhost', 'root', '');
    $result = mysqli_query($link,$sql);
    
	$sql = "SELECT m.miss_nr, SUM(p.num_inhabit) 'Expossed Inhabitants'
			FROM mission m, country_mission pm, country p
			WHERE m.miss_nr=pm.mission_nr AND pm.code=p.code
			GROUP BY m.miss_nr";

    $result = mysqli_query($link,$sql);
    $aux = "";

    while ($row = mysqli_fetch_row($result)) {
    	$aux = $aux . "Mission $row[0]: $row[1] inhabitants, ";
	}
	$aux = substr($aux, 0, -2);
	$aux = $aux . ".";
	echo $aux;

}

function BestHero() {
    $sql = "USE web_db";
    $link = mysqli_connect('localhost', 'root', '');
    $result = mysqli_query($link,$sql);
    
    $sql = "SELECT m.id, SUM(p.num_inhabit) 'saves'
            FROM mecha m, mech_mission mm, country_mission pm, country p
            WHERE m.mec_id=mm.mec_id AND mm.mission_nr=pm.mission_nr AND pm.code=p.code
            GROUP BY 'saves', m.id
            ORDER BY SUM(p.num_inhabit)
            DESC
            LIMIT 3";

    $result = mysqli_query($link,$sql);
    $aux = "";

    while ($row = mysqli_fetch_row($result)) {
    	$aux = $aux . "ID: $row[0] saved $row[1] inhabitants -- ";
	}
	$aux = substr($aux, 0, -4);
	$aux = $aux . ".";
	echo $aux;


}


?>